notebooks/2.2 Tidy data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2.2 Tidy data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this notebook, we take a closer look at the BL books dataset and tidy it up."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# imports\n",
    "\n",
    "import pandas as pd\n",
    "import json, os, codecs\n",
    "from collections import defaultdict, OrderedDict\n",
    "import seaborn as sns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import the dataset\n",
    "Let us import the sample dataset in memory, as it is, without transformations. We rely on some Python libraries to do so."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "root_folder = \"../data/bl_books/sample/\"\n",
    "\n",
    "# metadata\n",
    "filename = \"book_data_sample.json\"\n",
    "metadata = json.load(codecs.open(os.path.join(root_folder,filename), encoding=\"utf8\"))\n",
    "\n",
    "# fulltexts\n",
    "foldername = \"full_texts\"\n",
    "texts = defaultdict(list)\n",
    "for root, dirs, files in os.walk(os.path.join(root_folder,foldername)):\n",
    "    for f in files:\n",
    "        if \".json\" in f:\n",
    "            t = json.load(codecs.open(os.path.join(root,f), encoding=\"utf8\"))\n",
    "            texts[f] = t\n",
    "\n",
    "# enriched metadata\n",
    "filename = \"extra_metadata_sample.csv\"\n",
    "df_extra = pd.read_csv(os.path.join(root_folder,filename), delimiter=\";\")\n",
    "df_extra = df_extra.rename(str.lower, axis='columns') # rename columns to lower case"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Take a look\n",
    "Let's take a look to the dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "452\n"
     ]
    }
   ],
   "source": [
    "# there are 452 books in the sample\n",
    "\n",
    "print(len(metadata))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'datefield': '1841',\n",
       " 'shelfmarks': ['British Library HMNTS 11601.ddd.2.'],\n",
       " 'publisher': 'Privately printed',\n",
       " 'title': [\"The Poetical Aviary, with a bird's-eye view of the English poets. [The preface signed: A. A.] Ms. notes\"],\n",
       " 'edition': '',\n",
       " 'flickr_url_to_book_images': 'http://www.flickr.com/photos/britishlibrary/tags/sysnum000000196',\n",
       " 'place': 'Calcutta',\n",
       " 'issuance': 'monographic',\n",
       " 'authors': {'creator': ['A. A.']},\n",
       " 'date': '1841',\n",
       " 'pdf': {'1': 'lsidyv35c55757'},\n",
       " 'identifier': '000000196',\n",
       " 'corporate': {},\n",
       " 'fulltext_filename': 'sample/full_texts/000000196_01_text.json'}"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# each one contains the following catalog metadata\n",
    "\n",
    "metadata[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Questions** on 'metadata':\n",
    "\n",
    "* Can you identify some messy aspects of this dataset representation?\n",
    "* Take a look at the 'shelfmarks' or 'title' fields: what is the problem here?\n",
    "* Do the same for the 'authors' and 'pdf' fields: what is the problem?\n",
    "* Look at the datefield of the *third* item in this list: is there a problem?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "452\n"
     ]
    }
   ],
   "source": [
    "# let's check we have the same amount of books with a text file\n",
    "\n",
    "print(len(texts))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Note: we have selected for the sample just the first volume/pdf for every book.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[[1, ''],\n",
       " [2, ''],\n",
       " [3, ''],\n",
       " [4, ''],\n",
       " [5, ''],\n",
       " [6, ''],\n",
       " [7,\n",
       "  \"THE POETICAL AVIARY, WITH A B I R D'S-E YE VIEW OF THE ENGLISH POETS. (NOT PUBLISHED.) CALCUTTA: PRINTED AT THE BAPTIST MISSION PRESS, CIRCULAR ROAD. 1841.\"],\n",
       " [8, ''],\n",
       " [9,\n",
       "  'POETICAL AVIARY. PART THE FIRST. BIRDS WITHOUT ALLUSION TO THEIR NOTES. One of the curious political medals that were struck in the reign of Charles II. represents, on one side, Titus Oates with two faces. On the reverse are the heads of the king and four of his principal ministers, with this motto round the border, \" Birds of a feather flock together.\" This, as well as various other proverbs derived from birds, haee been introduced into poetry. Thus Anstey — And \\'twas pretty to see how like birds of a feather The people of quality flocked all together, All pressing, addressing, caressing, and fond, Just the same as those animals do in a pond. Under the sign of an inn representing a man with a bird in his hand, and two birds in a bush I have seen written, \" A bird in the hand\" d\\'ye see , Is worth two in the bushes that be. These verses under signs are now a rarity in England ; they were formerly more common. Swift, speaking of Harley, Earl of Oxford, the prime minister, says — Would take me in his coach to chat, And question me of this or that ; Or gravely try to read the lines Writ underneath the country signs. ii']]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# each text comes as a list of lists: one per page, as follows [page number, text]\n",
    "\n",
    "texts['000000196_01_text.json'][:9]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>aleph system no.</th>\n",
       "      <th>country code</th>\n",
       "      <th>language code (008)</th>\n",
       "      <th>language code (041)</th>\n",
       "      <th>ddc</th>\n",
       "      <th>personal author</th>\n",
       "      <th>corporate author</th>\n",
       "      <th>title</th>\n",
       "      <th>edition</th>\n",
       "      <th>imprint</th>\n",
       "      <th>series</th>\n",
       "      <th>subjects</th>\n",
       "      <th>other personal authors</th>\n",
       "      <th>other corporate authors</th>\n",
       "      <th>dom id</th>\n",
       "      <th>type</th>\n",
       "      <th>genre</th>\n",
       "      <th>first_pdf</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>234</th>\n",
       "      <td>14846757</td>\n",
       "      <td>|||</td>\n",
       "      <td>eng</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A. A.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>The Poetical Aviary, with a bird's-eye view of...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Calcutta : Privately printed, 1841.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>lsidyv35c55757</td>\n",
       "      <td>poet</td>\n",
       "      <td>Poetry</td>\n",
       "      <td>lsidyv35c55757</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     aleph system no. country code language code (008) language code (041)  \\\n",
       "234          14846757          |||                 eng                 NaN   \n",
       "\n",
       "     ddc personal author corporate author  \\\n",
       "234  NaN           A. A.              NaN   \n",
       "\n",
       "                                                 title edition  \\\n",
       "234  The Poetical Aviary, with a bird's-eye view of...     NaN   \n",
       "\n",
       "                                 imprint series subjects  \\\n",
       "234  Calcutta : Privately printed, 1841.    NaN      NaN   \n",
       "\n",
       "    other personal authors  other corporate authors          dom id  type  \\\n",
       "234                    NaN                      NaN  lsidyv35c55757  poet   \n",
       "\n",
       "      genre       first_pdf  \n",
       "234  Poetry  lsidyv35c55757  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the extra metadata can be already used as a data frame\n",
    "\n",
    "df_extra[df_extra[\"first_pdf\"] == \"lsidyv35c55757\"]\n",
    "# we perform here a selection of rows which abide to a given condition. We'll see more of this later on."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Question**: explore this data frame and find examples of messy aspects."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create data frames for all datasets\n",
    "# We drop some variables we don't need at this stage\n",
    "\n",
    "# metadata\n",
    "datefield = list() # '1841'\n",
    "publisher = list() # 'Privately printed',\n",
    "title = list() # [\"The Poetical Aviary, with a bird's-eye view of the English poets. [The preface signed: A. A.] Ms. notes\"]\n",
    "edition = list() # ''\n",
    "place = list() # 'Calcutta'\n",
    "issuance = list() # 'monographic'\n",
    "authors = list() # {'creator': ['A. A.']}\n",
    "first_pdf = list() # {'1': 'lsidyv35c55757'}\n",
    "number_volumes = list()\n",
    "identifier = list() # '000000196'\n",
    "fulltext_filename = list() # 'sample/full_texts/000000196_01_text.json'\n",
    "for book in metadata:\n",
    "    if book[\"date\"]:\n",
    "        datefield.append(int(book[\"date\"][:4]))\n",
    "    else:\n",
    "        datefield.append(None)\n",
    "    publisher.append(book[\"publisher\"])\n",
    "    title.append(book[\"title\"][0])\n",
    "    edition.append(book[\"edition\"])\n",
    "    place.append(book[\"place\"])\n",
    "    issuance.append(book[\"issuance\"])\n",
    "    if \"creator\" in book[\"authors\"].keys():\n",
    "        authors.append(book[\"authors\"][\"creator\"]) # this is a list!\n",
    "    else:\n",
    "        authors.append([''])\n",
    "    first_pdf.append(book[\"pdf\"][\"1\"])\n",
    "    number_volumes.append(len(book[\"pdf\"]))\n",
    "    identifier.append(book[\"identifier\"])\n",
    "    fulltext_filename.append(book[\"fulltext_filename\"].split(\"/\")[-1])\n",
    "df_meta = pd.DataFrame.from_dict({\"datefield\": datefield, \"publisher\": publisher,\n",
    "                                 \"title\": title, \"edition\": edition, \"place\": place,\n",
    "                                 \"issuance\": issuance, \"authors\": authors, \"first_pdf\": first_pdf,\n",
    "                                 \"number_volumes\": number_volumes, \"identifier\": identifier,\n",
    "                                 \"fulltext_filename\": fulltext_filename})\n",
    "\n",
    "# texts\n",
    "how_many_pages = 50 # we reduce the amount of text to the first n pages, to make it faster to play with it\n",
    "fulltext_filename = list()\n",
    "fulltext = list()\n",
    "for f,t in texts.items():\n",
    "    fulltext_filename.append(f)\n",
    "    text = \" \".join([line[1] for line in t][:how_many_pages])\n",
    "    fulltext.append(text)\n",
    "\n",
    "df_texts = pd.DataFrame.from_dict({\"fulltext_filename\": fulltext_filename, \"fulltext\": fulltext})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>datefield</th>\n",
       "      <th>publisher</th>\n",
       "      <th>title</th>\n",
       "      <th>edition</th>\n",
       "      <th>place</th>\n",
       "      <th>issuance</th>\n",
       "      <th>authors</th>\n",
       "      <th>first_pdf</th>\n",
       "      <th>number_volumes</th>\n",
       "      <th>identifier</th>\n",
       "      <th>fulltext_filename</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1841.0</td>\n",
       "      <td>Privately printed</td>\n",
       "      <td>The Poetical Aviary, with a bird's-eye view of...</td>\n",
       "      <td></td>\n",
       "      <td>Calcutta</td>\n",
       "      <td>monographic</td>\n",
       "      <td>[A. A.]</td>\n",
       "      <td>lsidyv35c55757</td>\n",
       "      <td>1</td>\n",
       "      <td>000000196</td>\n",
       "      <td>000000196_01_text.json</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1888.0</td>\n",
       "      <td>Rivingtons</td>\n",
       "      <td>A History of Greece. Part I. From the earliest...</td>\n",
       "      <td></td>\n",
       "      <td>London</td>\n",
       "      <td>monographic</td>\n",
       "      <td>[Abbott, Evelyn]</td>\n",
       "      <td>lsidyv376da437</td>\n",
       "      <td>1</td>\n",
       "      <td>000004047</td>\n",
       "      <td>000004047_01_text.json</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1847.0</td>\n",
       "      <td>Punch Office</td>\n",
       "      <td>The Comic History of England ... With ... colo...</td>\n",
       "      <td></td>\n",
       "      <td>London</td>\n",
       "      <td>monographic</td>\n",
       "      <td>[A'BECKETT, Gilbert Abbott.]</td>\n",
       "      <td>lsidyv38b27a31</td>\n",
       "      <td>1</td>\n",
       "      <td>000005382</td>\n",
       "      <td>000005382_01_text.json</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1892.0</td>\n",
       "      <td>Eden, Remington &amp; Co.</td>\n",
       "      <td>The Cruise of “The Tomahawk”: the story of a s...</td>\n",
       "      <td></td>\n",
       "      <td>London</td>\n",
       "      <td>monographic</td>\n",
       "      <td>[Laffan, De Courcy - Mrs]</td>\n",
       "      <td>lsidyv3c4a946a</td>\n",
       "      <td>1</td>\n",
       "      <td>000014627</td>\n",
       "      <td>000014627_01_text.json</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1863.0</td>\n",
       "      <td>Virtue Bros. &amp; Co.</td>\n",
       "      <td>Scenes from the Drama of European History</td>\n",
       "      <td></td>\n",
       "      <td>London</td>\n",
       "      <td>monographic</td>\n",
       "      <td>[Adams, W. H. Davenport (William Henry Davenpo...</td>\n",
       "      <td>lsidyv32c34c22</td>\n",
       "      <td>1</td>\n",
       "      <td>000017057</td>\n",
       "      <td>000017057_01_text.json</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   datefield              publisher  \\\n",
       "0     1841.0      Privately printed   \n",
       "1     1888.0             Rivingtons   \n",
       "2     1847.0           Punch Office   \n",
       "3     1892.0  Eden, Remington & Co.   \n",
       "4     1863.0     Virtue Bros. & Co.   \n",
       "\n",
       "                                               title edition     place  \\\n",
       "0  The Poetical Aviary, with a bird's-eye view of...          Calcutta   \n",
       "1  A History of Greece. Part I. From the earliest...            London   \n",
       "2  The Comic History of England ... With ... colo...            London   \n",
       "3  The Cruise of “The Tomahawk”: the story of a s...            London   \n",
       "4          Scenes from the Drama of European History            London   \n",
       "\n",
       "      issuance                                            authors  \\\n",
       "0  monographic                                            [A. A.]   \n",
       "1  monographic                                   [Abbott, Evelyn]   \n",
       "2  monographic                       [A'BECKETT, Gilbert Abbott.]   \n",
       "3  monographic                          [Laffan, De Courcy - Mrs]   \n",
       "4  monographic  [Adams, W. H. Davenport (William Henry Davenpo...   \n",
       "\n",
       "        first_pdf  number_volumes identifier       fulltext_filename  \n",
       "0  lsidyv35c55757               1  000000196  000000196_01_text.json  \n",
       "1  lsidyv376da437               1  000004047  000004047_01_text.json  \n",
       "2  lsidyv38b27a31               1  000005382  000005382_01_text.json  \n",
       "3  lsidyv3c4a946a               1  000014627  000014627_01_text.json  \n",
       "4  lsidyv32c34c22               1  000017057  000017057_01_text.json  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_meta.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fulltext_filename</th>\n",
       "      <th>fulltext</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000551646_01_text.json</td>\n",
       "      <td>' -■\" ' LiLitr-- )Wm&amp;, HISTORY OF THE...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        fulltext_filename                                           fulltext\n",
       "0  000551646_01_text.json           ' -■\" ' LiLitr-- )Wm&, HISTORY OF THE..."
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_texts.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## A second look\n",
    "Let's check data types and typical values to be sure of what we have."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datefield            float64\n",
       "publisher             object\n",
       "title                 object\n",
       "edition               object\n",
       "place                 object\n",
       "issuance              object\n",
       "authors               object\n",
       "first_pdf             object\n",
       "number_volumes         int64\n",
       "identifier            object\n",
       "fulltext_filename     object\n",
       "dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_meta.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x1a20473668>"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXcAAAD4CAYAAAAXUaZHAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjAsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+17YcXAAARvElEQVR4nO3dfYxcV3nH8e9DTMLLEtup6Tay3a4RLm2IC41HaYACu4RCIBRHLWmDIrAh1Yoq0BRSKU6pyB8tqhFQXvoCspoII9FsQkgbNyEFy2WJkLDBGwLOK3aCG+ykcSGJYQFBXT39Y+4qE2c29s6d8XjOfD/Sau+ce+/c8xzP/vb67J07kZlIksryjH53QJLUfYa7JBXIcJekAhnuklQgw12SCrSo3x0AWLZsWY6NjfW7G33xk5/8hOc+97n97kZfDfsYDHv94Bh0Wv/MzMwPMvP57dadEOE+NjbGrl27+t2NvpienmZ8fLzf3eirYR+DYa8fHINO64+I/5pvndMyklQgw12SCmS4S1KBDHdJKtBRwz0iromIgxFxZ0vbhyPi3oj4TkT8a0QsaVl3ZUTsjYj7IuL1veq4JGl+x3Lm/hngvCPatgFnZuZvAd8FrgSIiDOAi4AXV/v8U0Sc1LXeSpKOyVHDPTNvAx49ou3LmXm4ergDWFEtrwOmMvPnmfk9YC9wdhf7K0k6Bt24zv2dwHXV8nKaYT9nf9X2FBExCUwCjI6OMj093YWuDJ7Z2dmhrX3OsI/BsNcPjkEv6q8V7hHxfuAw8Lm5pjabtb1hfGZuBjYDNBqNHNY3MAz7mzfAMRj2+sEx6EX9HYd7RKwH3gScm0984sd+YGXLZiuAhzrvniSd+MY23tLxvvs2nd/Fnjyho0shI+I84ArgzZn505ZVW4GLIuKUiFgFrAa+Ub+bkqSFOOqZe0RcC4wDyyJiP3AVzatjTgG2RQTAjsx8V2beFRHXA3fTnK65NDP/r1edlyS1d9Rwz8y3tmm++mm2/yDwwTqdkiTV4ztUJalAhrskFchwl6QCGe6SVCDDXZIKZLhLUoEMd0kqkOEuSQUy3CWpQIa7JBXIcJekAhnuklQgw12SCmS4S1KBDHdJKpDhLkkFMtwlqUCGuyQVyHCXpAIZ7pJUIMNdkgpkuEtSgQx3SSqQ4S5JBTLcJalAhrskFeio4R4R10TEwYi4s6XttIjYFhF7qu9Lq/aIiE9GxN6I+E5EnNXLzkuS2juWM/fPAOcd0bYR2J6Zq4Ht1WOANwCrq69J4FPd6aYkaSGOGu6ZeRvw6BHN64At1fIW4IKW9s9m0w5gSUSc3q3OSpKOTWTm0TeKGANuzswzq8ePZ+aSlvWPZebSiLgZ2JSZX6vatwNXZOauNs85SfPsntHR0bVTU1NdKGfwzM7OMjIy0u9u9NWwj8Gw1w+DPwa7DxzqeN81yxd3XP/ExMRMZjbarVvUcY/aizZtbX97ZOZmYDNAo9HI8fHxLndlMExPTzOstc8Z9jEY9vph8Mdgw8ZbOt5338XjPam/06tlHpmbbqm+H6za9wMrW7ZbATzUefckSZ3oNNy3Auur5fXATS3tb6+umjkHOJSZD9fsoyRpgY46LRMR1wLjwLKI2A9cBWwCro+IS4AHgQurzb8IvBHYC/wUeEcP+ixJOoqjhntmvnWeVee22TaBS+t2SpJUj+9QlaQCGe6SVCDDXZIKZLhLUoEMd0kqkOEuSQUy3CWpQIa7JBXIcJekAhnuklQgw12SCmS4S1KBDHdJKpDhLkkFMtwlqUCGuyQVyHCXpAIZ7pJUIMNdkgpkuEtSgQx3SSqQ4S5JBTLcJalAhrskFchwl6QC1Qr3iHhvRNwVEXdGxLUR8ayIWBUROyNiT0RcFxEnd6uzkqRj03G4R8Ry4M+ARmaeCZwEXAR8CPhYZq4GHgMu6UZHJUnHru60zCLg2RGxCHgO8DDwGuCGav0W4IKax5AkLVDH4Z6ZB4CPAA/SDPVDwAzweGYerjbbDyyv20lJ0sJEZna2Y8RS4AvAHwOPA5+vHl+VmS+stlkJfDEz17TZfxKYBBgdHV07NTXVUT8G3ezsLCMjI/3uRl8N+xgMe/0w+GOw+8Chjvdds3xxx/VPTEzMZGaj3bpFHfcIXgt8LzP/ByAibgReDiyJiEXV2fsK4KF2O2fmZmAzQKPRyPHx8RpdGVzT09MMa+1zhn0Mhr1+GPwx2LDxlo733XfxeE/qrzPn/iBwTkQ8JyICOBe4G/gK8JZqm/XATfW6KElaqDpz7jtp/uH0dmB39VybgSuA90XEXuCXgKu70E9J0gLUmZYhM68Crjqi+QHg7DrPK0mqx3eoSlKBDHdJKpDhLkkFMtwlqUCGuyQVyHCXpAIZ7pJUIMNdkgpkuEtSgQx3SSqQ4S5JBTLcJalAtW4cJkklGKtxP/YTlWfuklQgw12SCmS4S1KBDHdJKpDhLkkFMtwlqUCGuyQVyHCXpAIZ7pJUIMNdkgpkuEtSgQx3SSqQ4S5JBaoV7hGxJCJuiIh7I+KeiHhZRJwWEdsiYk/1fWm3OitJOjZ1z9w/AfxHZv4G8BLgHmAjsD0zVwPbq8eSpOOo43CPiFOBVwFXA2TmLzLzcWAdsKXabAtwQd1OSpIWJjKzsx0jXgpsBu6medY+A1wGHMjMJS3bPZaZT5maiYhJYBJgdHR07dTUVEf9GHSzs7OMjIz0uxt9NexjMOz1Q//HYPeBQ3079prlizuuf2JiYiYzG+3W1Qn3BrADeEVm7oyITwA/At5zLOHeqtFo5K5duzrqx6Cbnp5mfHy8393oq2Efg2GvH/o/Bv38JKZ9m87vuP6ImDfc68y57wf2Z+bO6vENwFnAIxFxenXg04GDNY4hSepAx+Gemf8NfD8iXlQ1nUtzimYrsL5qWw/cVKuHkqQFq/sB2e8BPhcRJwMPAO+g+Qvj+oi4BHgQuLDmMSRJC1Qr3DPzDqDdfM+5dZ5XklSP71CVpAIZ7pJUIMNdkgpkuEtSgQx3SSqQ4S5JBTLcJalAhrskFchwl6QCGe6SVKC695aRpBNCP2/beyLyzF2SCmS4S1KBDHdJKpDhLkkFMtwlqUCGuyQVyHCXpAIZ7pJUIMNdkgpkuEtSgQx3SSqQ4S5JBTLcJalAhrskFchwl6QC1Q73iDgpIr4VETdXj1dFxM6I2BMR10XEyfW7KUlaiG58WMdlwD3AqdXjDwEfy8ypiPg0cAnwqS4cR9IA6ORDMy5fc5gNG29h36bze9Cj4VTrzD0iVgDnA/9cPQ7gNcAN1SZbgAvqHEOStHCRmZ3vHHED8LfA84C/ADYAOzLzhdX6lcCtmXlmm30ngUmA0dHRtVNTUx33Y5DNzs4yMjLS72701bCPQWn17z5waMH7jD4bHvkZrFm++Lge90SwZvnijl8DExMTM5nZaLeu42mZiHgTcDAzZyJifK65zaZtf3tk5mZgM0Cj0cjx8fF2mxVvenqaYa19zrCPQWn1b+hwWuajuxex7+Lx43rcE8G+i8d78hqoM+f+CuDNEfFG4Fk059w/DiyJiEWZeRhYATxUv5uSpIXoeM49M6/MzBWZOQZcBPxnZl4MfAV4S7XZeuCm2r2UJC1IN66WOdIVwFRE/A3wLeDqHhxD0tPo5IqVOV6xUoauhHtmTgPT1fIDwNndeF5JUmd8h6okFagX0zKS1JE600l6Ms/cJalAhrskFchwl6QCGe6SVCDDXZIKZLhLUoEMd0kqkOEuSQUy3CWpQIa7JBXIcJekAhnuklQgw12SCmS4S1KBDHdJKpDhLkkFMtwlqUB+EpOkJ/HTkMrgmbskFchwl6QCGe6SVCDDXZIKZLhLUoEMd0kqUMfhHhErI+IrEXFPRNwVEZdV7adFxLaI2FN9X9q97kqSjkWdM/fDwOWZ+ZvAOcClEXEGsBHYnpmrge3VY0nScdRxuGfmw5l5e7X8Y+AeYDmwDthSbbYFuKBuJyVJCxOZWf9JIsaA24AzgQczc0nLuscy8ylTMxExCUwCjI6Orp2amqrdj0E0OzvLyMhIv7vRV8M+Br2of/eBQ119vl4bfTY88rN+96I/1ixf3PFrYGJiYiYzG+3W1Q73iBgBvgp8MDNvjIjHjyXcWzUajdy1a1etfgyq6elpxsfH+92Nvhr2MehF/YN2C4HL1xzmo7uH824o+zad3/FrICLmDfdaV8tExDOBLwCfy8wbq+ZHIuL0av3pwME6x5AkLVzHvyojIoCrgXsy8+9aVm0F1gObqu831eqhNMCO5Qz68jWH2dBmu32bzu9FlzQk6vw/6BXA24DdEXFH1faXNEP9+oi4BHgQuLBeFyVJC9VxuGfm14CYZ/W5nT6vpKZBmzfXicV3qEpSgQx3SSqQ4S5JBTLcJalAhrskFchwl6QCGe6SVCDDXZIKNJx36tFQqftmIG8DoEHkmbskFchwl6QCGe6SVCDDXZIKZLhLUoEMd0kqkOEuSQUy3CWpQIa7JBXId6hqIPiRc9LCeOYuSQUy3CWpQIa7JBXIOfca6swDe6dBSb3kmbskFWjgz9y9V/fgmO/f6vI1h9ng1TBSVw18uNc1jJfYOZ0kla9n0zIRcV5E3BcReyNiY6+OI0l6qp6cuUfEScA/Ar8H7Ae+GRFbM/PuXhxPx4//05EGQ6/O3M8G9mbmA5n5C2AKWNejY0mSjhCZ2f0njXgLcF5m/kn1+G3A72Tmu1u2mQQmq4cvAu7rekcGwzLgB/3uRJ8N+xgMe/3gGHRa/69l5vPbrejVH1SjTduTfotk5mZgc4+OPzAiYldmNvrdj34a9jEY9vrBMehF/b2altkPrGx5vAJ4qEfHkiQdoVfh/k1gdUSsioiTgYuArT06liTpCD2ZlsnMwxHxbuBLwEnANZl5Vy+OVYChn5rCMRj2+sEx6Hr9PfmDqiSpv7y3jCQVyHCXpAIZ7j0QEddExMGIuLOl7bqIuKP62hcRd7Ssu7K6TcN9EfH6lvaBvIXDQuqPiLGI+FnLuk+37LM2InZX9X8yItpdYntCmmcMXhoRO6o6d0XE2VV7VPXtjYjvRMRZLfusj4g91df6ftTSiQXWPx4Rh1peAx9o2WcgfwZg3jF4SUR8vXpd/3tEnNqyrrs5kJl+dfkLeBVwFnDnPOs/CnygWj4D+DZwCrAKuJ/mH6FPqpZfAJxcbXNGv2vrQf1jT7PdN4CX0XzfxK3AG/pdW50xAL48VwPwRmC6ZfnWqs5zgJ1V+2nAA9X3pdXy0n7X1oP6x4Gb2zzHwP4MPM0YfBN4dbX8TuCvq+Wu54Bn7j2QmbcBj7ZbV519/hFwbdW0DpjKzJ9n5veAvTRv3zCwt3BYYP1tRcTpwKmZ+fVsvvo/C1zQ7b72yjxjkMDcmdpinnjvxzrgs9m0A1hS1f96YFtmPpqZjwHbgPN63/v6Flj/fAb2ZwDmHYMXAbdVy9uAP6yWu54Dhvvx90rgkczcUz1eDny/Zf3+qm2+9kF3ZP0AqyLiWxHx1Yh4ZdW2nGbNc0qo/8+BD0fE94GPAFdW7cPyGpivfoCXRcS3I+LWiHhx1VZa/QB3Am+uli/kiTd7dv01YLgff2/lyWet892q4ai3cBhQR9b/MPCrmfnbwPuAf6nmIUus/0+B92bmSuC9wNVV+7C8Buar/3aa90h5CfD3wL9V7aXVD82pmEsjYgZ4HvCLqr3rrwHD/TiKiEXAHwDXtTTPd6uG4m7h0K7+6r+hP6yWZ2jOL/46zfpXtOw+8PUD64Ebq+XP0/wvNwzPa6Bt/Zn5o8ycrZa/CDwzIpZRXv1k5r2Z+brMXEvzJOf+alXXXwOG+/H1WuDezGydbtgKXBQRp0TEKmA1zT8klngLh6fUHxHPj+b9/4mIF9Cs/4HMfBj4cUScU83Tvx24qR+d7qKHgFdXy68B5qamtgJvr66aOQc4VNX/JeB1EbE0IpYCr6vaBlXb+iPiV+auhKquoHkG8EMK/BmIiF+uvj8D+Ctg7uqw7udAv/+iXOIXzd/IDwP/S/M37yVV+2eAd7XZ/v00f4PfR8sVITSvKPhute79/a6rF/XT/IPSXTSvArgd+P2WdQ2ac5T3A/9A9Y7qQfhqNwbA7wIzVa07gbXVtkHzw23uB3YDjZbneSfNP67tBd7R77p6VP+7W14DO4CXtzzPQP4MPM0YXFbV811gU+truts54O0HJKlATstIUoEMd0kqkOEuSQUy3CWpQIa7JBXIcJekAhnuklSg/wdsGT2o6OmvugAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_meta[\"datefield\"].hist(bins=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x1a25675ef0>"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXcAAAD4CAYAAAAXUaZHAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjAsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+17YcXAAAV/klEQVR4nO3df5BddX3G8fcjv2ExQcGdmGQaOqSMSMZA7gCWGecusTaAY3BGOjApBqWzdgYdrLQF/EetpY1TI1ZKmVkJEmrKmgaYZCJYaciWMiMgi4EEokOULS6JWWlCYDHSgp/+cb8Zl+Xu3t97c74+r5mdvefH997nZODZs2fPvV9FBGZmlpe3dTuAmZm1n8vdzCxDLnczswy53M3MMuRyNzPL0JHdDgBw8sknx4IFC5oa++qrr3LCCSe0N1AHFSlvkbJCsfIWKSsUK2+RskJreYeHh1+MiFOqboyIrn8tWbIkmrV169amx3ZDkfIWKWtEsfIWKWtEsfIWKWtEa3mBx2OKXvVlGTOzDLnczcwy5HI3M8uQy93MLEMudzOzDLnczcwy5HI3M8uQy93MLEMudzOzDB0WHz/Qiu0vHODK67/bldceWXVxV17XzKwWn7mbmWXI5W5mliGXu5lZhlzuZmYZcrmbmWXI5W5mliGXu5lZhlzuZmYZcrmbmWWo7nKXdISkH0nanJZPlfSopGclfUfS0Wn9MWl5V9q+oDPRzcxsKo2cuV8D7Jyw/BXgpohYCOwHrkrrrwL2R8RpwE1pPzMzm0F1lbukecDFwG1pWcAFwIa0y1rgkvR4eVombV+a9jczsxmiiKi9k7QB+HvgROAvgSuBR9LZOZLmA/dHxJmSdgDLImI0bfspcG5EvDjpOfuBfoDe3t4lg4ODTR3A2L4D7D3Y1NCWLZo7q+Ex4+Pj9PT0dCBN+xUpKxQrb5GyQrHyFikrtJa3r69vOCJK1bbV/FRISR8GxiJiWFL50Ooqu0Yd2367ImIAGAAolUpRLpcn71KXm9dtZPX27ny45ciKcsNjhoaGaPZYZ1qRskKx8hYpKxQrb5GyQufy1tOK5wMfkXQRcCzwduDrwGxJR0bE68A8YHfafxSYD4xKOhKYBexre3IzM5tSzWvuEXFDRMyLiAXAZcCDEbEC2Ap8LO22EtiYHm9Ky6TtD0Y9137MzKxtWrnP/Trgc5J2Ae8E1qT1a4B3pvWfA65vLaKZmTWqoYvVETEEDKXHPwPOqbLPr4FL25DNzMya5HeompllyOVuZpYhl7uZWYZc7mZmGXK5m5llyOVuZpYhl7uZWYZc7mZmGXK5m5llyOVuZpYhl7uZWYZc7mZmGXK5m5llyOVuZpYhl7uZWYZqlrukYyU9JulJSU9L+lJaf4ek5yRtS1+L03pJ+oakXZKeknR2pw/CzMzerJ7JOl4DLoiIcUlHAQ9Luj9t+6uI2DBp/wuBhenrXODW9N3MzGZIPXOoRkSMp8Wj0td0c6IuB+5M4x6hMpH2nNajmplZvVTP3NWSjgCGgdOAWyLiOkl3AO+ncma/Bbg+Il6TtBlYFREPp7FbgOsi4vFJz9kP9AP09vYuGRwcbOoAxvYdYO/Bpoa2bNHcWQ2PGR8fp6enpwNp2q9IWaFYeYuUFYqVt0hZobW8fX19wxFRqratrjlUI+INYLGk2cC9ks4EbgB+ARwNDFCZMPtvAFV7iirPOZDGUSqVolwu1xPlLW5et5HV2xuaCrZtRlaUGx4zNDREs8c604qUFYqVt0hZoVh5i5QVOpe3obtlIuIlKhNkL4uIPenSy2vAt/jtZNmjwPwJw+YBu9uQ1czM6lTP3TKnpDN2JB0HfBD48aHr6JIEXALsSEM2AR9Pd82cBxyIiD0dSW9mZlXVcz1jDrA2XXd/G7A+IjZLelDSKVQuw2wD/jztfx9wEbAL+BXwifbHNjOz6dQs94h4CjiryvoLptg/gKtbj2ZmZs3yO1TNzDLkcjczy5DL3cwsQy53M7MMudzNzDLkcjczy5DL3cwsQy53M7MMudzNzDLkcjczy5DL3cwsQy53M7MMudzNzDLkcjczy5DL3cwsQ/XMxHSspMckPSnpaUlfSutPlfSopGclfUfS0Wn9MWl5V9q+oLOHYGZmk9Vz5v4acEFEvA9YDCxL0+d9BbgpIhYC+4Gr0v5XAfsj4jTgprSfmZnNoJrlnibBHk+LR6WvAC4ANqT1a6nMowqwPC2Tti9N86yamdkMUWVWvBo7VeZPHQZOA24B/gF4JJ2dI2k+cH9EnClpB7AsIkbTtp8C50bEi5Oesx/oB+jt7V0yODjY1AGM7TvA3oNNDW3ZormzGh4zPj5OT09PB9K0X5GyQrHyFikrFCtvkbJCa3n7+vqGI6JUbVs9E2QTEW8AiyXNBu4F3lNtt/S92ln6W36CRMQAMABQKpWiXC7XE+Utbl63kdXb6zqMthtZUW54zNDQEM0e60wrUlYoVt4iZYVi5S1SVuhc3obulomIl4Ah4DxgtqRDrToP2J0ejwLzAdL2WcC+doQ1M7P61HO3zCnpjB1JxwEfBHYCW4GPpd1WAhvT401pmbT9wajn2o+ZmbVNPdcz5gBr03X3twHrI2KzpGeAQUl/C/wIWJP2XwP8i6RdVM7YL+tAbjMzm0bNco+Ip4Czqqz/GXBOlfW/Bi5tSzozM2uK36FqZpYhl7uZWYZc7mZmGXK5m5llyOVuZpYhl7uZWYZc7mZmGXK5m5llyOVuZpYhl7uZWYZc7mZmGXK5m5llyOVuZpYhl7uZWYZc7mZmGapnJqb5krZK2inpaUnXpPVflPSCpG3p66IJY26QtEvSTyT9cScPwMzM3qqemZheB66NiCcknQgMS3ogbbspIr46cWdJZ1CZfem9wLuB/5D0B2mSbTMzmwE1z9wjYk9EPJEev0Jl/tS50wxZDgxGxGsR8RywiyozNpmZWec0dM1d0gIqU+49mlZ9WtJTkm6XdFJaNxf4+YRho0z/w8DMzNpMEVHfjlIP8J/AjRFxj6Re4EUggC8DcyLik5JuAX4QEd9O49YA90XE3ZOerx/oB+jt7V0yODjY1AGM7TvA3oNNDW3ZormzGh4zPj5OT09PB9K0X5GyQrHyFikrFCtvkbJCa3n7+vqGI6JUbVs919yRdBRwN7AuIu4BiIi9E7Z/E9icFkeB+ROGzwN2T37OiBgABgBKpVKUy+V6orzFzes2snp7XYfRdiMryg2PGRoaotljnWlFygrFylukrFCsvEXKCp3LW8/dMgLWADsj4msT1s+ZsNtHgR3p8SbgMknHSDoVWAg81r7IZmZWSz2nvOcDVwDbJW1L6z4PXC5pMZXLMiPApwAi4mlJ64FnqNxpc7XvlDEzm1k1yz0iHgZUZdN904y5EbixhVxmZtYCv0PVzCxDLnczswy53M3MMuRyNzPLkMvdzCxDLnczswy53M3MMuRyNzPLkMvdzCxDLnczswy53M3MMuRyNzPLkMvdzCxDLnczswy53M3MMuRyNzPLUD3T7M2XtFXSTklPS7omrX+HpAckPZu+n5TWS9I3JO2S9JSkszt9EGZm9mb1nLm/DlwbEe8BzgOulnQGcD2wJSIWAlvSMsCFVOZNXQj0A7e2PbWZmU2rZrlHxJ6IeCI9fgXYCcwFlgNr025rgUvS4+XAnVHxCDB70mTaZmbWYYqI+neWFgAPAWcCz0fE7Anb9kfESZI2A6vS3KtI2gJcFxGPT3qufipn9vT29i4ZHBxs6gDG9h1g78GmhrZs0dxZDY8ZHx+np6enA2nar0hZoVh5i5QVipW3SFmhtbx9fX3DEVGqtq3mBNmHSOoB7gY+GxEvS9XmzK7sWmXdW36CRMQAMABQKpWiXC7XG+VNbl63kdXb6z6MthpZUW54zNDQEM0e60wrUlYoVt4iZYVi5S1SVuhc3rrulpF0FJViXxcR96TVew9dbknfx9L6UWD+hOHzgN3tiWtmZvWo524ZAWuAnRHxtQmbNgEr0+OVwMYJ6z+e7po5DzgQEXvamNnMzGqo53rG+cAVwHZJ29K6zwOrgPWSrgKeBy5N2+4DLgJ2Ab8CPtHWxGZmVlPNck9/GJ3qAvvSKvsHcHWLuczMrAV+h6qZWYZc7mZmGXK5m5llyOVuZpYhl7uZWYZc7mZmGXK5m5llyOVuZpYhl7uZWYZc7mZmGXK5m5llyOVuZpYhl7uZWYZc7mZmGXK5m5llqJ6ZmG6XNCZpx4R1X5T0gqRt6euiCdtukLRL0k8k/XGngpuZ2dTqOXO/A1hWZf1NEbE4fd0HIOkM4DLgvWnMP0s6ol1hzcysPjXLPSIeAvbV+XzLgcGIeC0inqMy1d45LeQzM7MmqDIrXo2dpAXA5og4My1/EbgSeBl4HLg2IvZL+ifgkYj4dtpvDXB/RGyo8pz9QD9Ab2/vksHBwaYOYGzfAfYebGpoyxbNndXwmPHxcXp6ejqQpv2KlBWKlbdIWaFYeYuUFVrL29fXNxwRpWrb6pkgu5pbgS8Dkb6vBj5J9blWq/70iIgBYACgVCpFuVxuKsjN6zayenuzh9GakRXlhscMDQ3R7LHOtCJlhWLlLVJWKFbeImWFzuVt6m6ZiNgbEW9ExG+Ab/LbSy+jwPwJu84DdrcW0czMGtVUuUuaM2Hxo8ChO2k2AZdJOkbSqcBC4LHWIpqZWaNqXs+QdBdQBk6WNAp8AShLWkzlkssI8CmAiHha0nrgGeB14OqIeKMz0c3MbCo1yz0iLq+yes00+98I3NhKKDMza43foWpmliGXu5lZhlzuZmYZcrmbmWXI5W5mliGXu5lZhlzuZmYZcrmbmWXI5W5mliGXu5lZhlzuZmYZcrmbmWXI5W5mliGXu5lZhlzuZmYZqlnukm6XNCZpx4R175D0gKRn0/eT0npJ+oakXZKeknR2J8ObmVl19Zy53wEsm7TuemBLRCwEtqRlgAupTK23EOinMpG2mZnNsJrlHhEPAfsmrV4OrE2P1wKXTFh/Z1Q8AsyeNN+qmZnNAEVE7Z2kBcDmiDgzLb8UEbMnbN8fESdJ2gysioiH0/otwHUR8XiV5+yncnZPb2/vksHBwaYOYGzfAfYebGpoyxbNndXwmPHxcXp6ejqQpv2KlBWKlbdIWaFYeYuUFVrL29fXNxwRpWrbas6h2iBVWVf1p0dEDAADAKVSKcrlclMvePO6jaze3u7DqM/IinLDY4aGhmj2WGdakbJCsfIWKSsUK2+RskLn8jZ7t8zeQ5db0vextH4UmD9hv3nA7ubjmZlZM5ot903AyvR4JbBxwvqPp7tmzgMORMSeFjOamVmDal7PkHQXUAZOljQKfAFYBayXdBXwPHBp2v0+4CJgF/Ar4BMdyGxmZjXULPeIuHyKTUur7BvA1a2GMjOz1vgdqmZmGXK5m5llyOVuZpYhl7uZWYZc7mZmGXK5m5llyOVuZpYhl7uZWYZc7mZmGXK5m5llyOVuZpYhl7uZWYZc7mZmGXK5m5llyOVuZpahliYflTQCvAK8AbweESVJ7wC+AywARoA/iYj9rcU0M7NGtOPMvS8iFk+Ygft6YEtELAS2pGUzM5tBnbgssxxYmx6vBS7pwGuYmdk0Wi33AL4vaVhSf1rXe2hS7PT9XS2+hpmZNUiVaU+bHCy9OyJ2S3oX8ADwGWBTRMyesM/+iDipyth+oB+gt7d3yeDgYFMZxvYdYO/Bpoa2bNHcWQ2PGR8fp6enpwNp2q9IWaFYeYuUFYqVt0hZobW8fX19wxMuib9JS39QjYjd6fuYpHuBc4C9kuZExB5Jc4CxKcYOAAMApVIpyuVyUxluXreR1dtbOoymjawoNzxmaGiIZo91phUpKxQrb5GyQrHyFikrdC5v05dlJJ0g6cRDj4EPATuATcDKtNtKYGOrIc3MrDGtnPL2AvdKOvQ8/xoR35P0Q2C9pKuA54FLW49pZmaNaLrcI+JnwPuqrP8fYGkroczMrDV+h6qZWYZc7mZmGXK5m5llyOVuZpYhl7uZWYZc7mZmGXK5m5llyOVuZpYhl7uZWYZc7mZmGXK5m5llqDuflZuJBdd/t+Ex1y56nSubGDfRyKqLWxpvZvnzmbuZWYZc7mZmGXK5m5llyOVuZpahjv1BVdIy4B+BI4DbImJVp17rd00zf8htRrU//vqPuWbF0JEzd0lHALcAFwJnAJdLOqMTr2VmZm/VqTP3c4BdaSo+JA0Cy4FnOvR6lrl6fltpx22m1fyu/bZS7d+6U/+2k3Xr33qmfhuu5o5lJ3TkeRUR7X9S6WPAsoj4s7R8BXBuRHx6wj79QH9aPB34SZMvdzLwYgtxZ1qR8hYpKxQrb5GyQrHyFikrtJb39yLilGobOnXmrirr3vRTJCIGgIGWX0h6PCJKrT7PTClS3iJlhWLlLVJWKFbeImWFzuXt1N0yo8D8CcvzgN0dei0zM5ukU+X+Q2ChpFMlHQ1cBmzq0GuZmdkkHbksExGvS/o08O9UboW8PSKe7sRr0YZLOzOsSHmLlBWKlbdIWaFYeYuUFTqUtyN/UDUzs+7yO1TNzDLkcjczy1Bhy13S7ZLGJO3odpZaJM2XtFXSTklPS7qm25mmI+lYSY9JejLl/VK3M9Ui6QhJP5K0udtZapE0Imm7pG2SHu92nulImi1pg6Qfp/9+39/tTFORdHr6Nz309bKkz3Y711Qk/UX6/2uHpLskHdvW5y/qNXdJHwDGgTsj4sxu55mOpDnAnIh4QtKJwDBwSUQclu/YlSTghIgYl3QU8DBwTUQ80uVoU5L0OaAEvD0iPtztPNORNAKUIuKwf6ONpLXAf0XEbenOt+Mj4qVu56olfQTKC1TePPnf3c4zmaS5VP6/OiMiDkpaD9wXEXe06zUKe+YeEQ8B+7qdox4RsScinkiPXwF2AnO7m2pqUTGeFo9KX4ftWYCkecDFwG3dzpITSW8HPgCsAYiI/y1CsSdLgZ8ejsU+wZHAcZKOBI6nze8FKmy5F5WkBcBZwKPdTTK9dJljGzAGPBARh3PerwN/Dfym20HqFMD3JQ2nj+E4XP0+8EvgW+mS122SOvNBKO13GXBXt0NMJSJeAL4KPA/sAQ5ExPfb+Rou9xkkqQe4G/hsRLzc7TzTiYg3ImIxlXcXnyPpsLz0JenDwFhEDHc7SwPOj4izqXxq6tXpEuPh6EjgbODWiDgLeBW4vruRakuXjz4C/Fu3s0xF0klUPkzxVODdwAmS/rSdr+FynyHp2vXdwLqIuKfbeeqVfg0fApZ1OcpUzgc+kq5jDwIXSPp2dyNNLyJ2p+9jwL1UPkX1cDQKjE74rW0DlbI/3F0IPBERe7sdZBofBJ6LiF9GxP8B9wB/2M4XcLnPgPQHyjXAzoj4Wrfz1CLpFEmz0+PjqPyH+OPupqouIm6IiHkRsYDKr+IPRkRbz4DaSdIJ6Y/qpEscHwIOyzu+IuIXwM8lnZ5WLaUYH9t9OYfxJZnkeeA8ScenflhK5W9xbVPYcpd0F/AD4HRJo5Ku6namaZwPXEHlrPLQbVoXdTvUNOYAWyU9ReVzgh6IiMP+FsOC6AUelvQk8Bjw3Yj4XpczTeczwLr038Ji4O+6nGdako4H/ojKmfBhK/02tAF4AthOpYvb+jEEhb0V0szMplbYM3czM5uay93MLEMudzOzDLnczcwy5HI3M8uQy93MLEMudzOzDP0/gSHPCO+hSh0AAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_meta[\"number_volumes\"].hist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "London          243\n",
       "New York         23\n",
       "Dublin           10\n",
       "Philadelphia     10\n",
       "Edinburgh         9\n",
       "enk               8\n",
       "Leeds             8\n",
       "Boston            6\n",
       "Cambridge         6\n",
       "                  5\n",
       "Oxford            5\n",
       "Name: place, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "variable = \"place\"\n",
    "df_meta[variable].value_counts()[:11]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Worthing, London [printed]                1\n",
       "Sheffield                                 1\n",
       "Harrogate                                 1\n",
       "Omaha, Neb                                1\n",
       "Edinburgh and London, London [printed]    1\n",
       "Liverpool                                 1\n",
       "Cirencester                               1\n",
       "printed by Henry Slatter                  1\n",
       "Minneapolis                               1\n",
       "Retford                                   1\n",
       "Name: place, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_meta[variable].value_counts()[-10:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Prose     120\n",
       "Poetry    120\n",
       "Drama     120\n",
       "Music     120\n",
       "Name: genre, dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_extra[\"genre\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "story         106\n",
       "opera          79\n",
       "play           64\n",
       "ode            53\n",
       "poet           37\n",
       "song           36\n",
       "tragedy        32\n",
       "comedy         18\n",
       "verse          10\n",
       "lines           9\n",
       "elegy           6\n",
       "recitation      6\n",
       "romance         6\n",
       "melody          3\n",
       "tale            3\n",
       "novel           3\n",
       "hymn            2\n",
       "rhyme           2\n",
       "lyric           2\n",
       "satire          2\n",
       "ballad          1\n",
       "Name: type, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_extra[\"type\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Questions**:\n",
    "    \n",
    "* 'place' seems like a reasonably uniform variable. Try with 'edition' instead, and think about how that might be more problematic.\n",
    "* While the 'genre' variable is uniform in representation, the 'type' variable is not. Can you find out what are the most well-represented types for each genre category? How might this influence our use of 'type' for analysis?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## UML modelling\n",
    "From an Entity-Relationship model to a relational model (tidy data).\n",
    "\n",
    "UML: Unified Modelling Language. A visual design language to go about modelling systems, including data. https://en.wikipedia.org/wiki/Unified_Modeling_Language"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>datefield</th>\n",
       "      <th>publisher</th>\n",
       "      <th>title</th>\n",
       "      <th>edition</th>\n",
       "      <th>place</th>\n",
       "      <th>issuance</th>\n",
       "      <th>authors</th>\n",
       "      <th>first_pdf</th>\n",
       "      <th>number_volumes</th>\n",
       "      <th>identifier</th>\n",
       "      <th>fulltext_filename</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1841.0</td>\n",
       "      <td>Privately printed</td>\n",
       "      <td>The Poetical Aviary, with a bird's-eye view of...</td>\n",
       "      <td></td>\n",
       "      <td>Calcutta</td>\n",
       "      <td>monographic</td>\n",
       "      <td>[A. A.]</td>\n",
       "      <td>lsidyv35c55757</td>\n",
       "      <td>1</td>\n",
       "      <td>000000196</td>\n",
       "      <td>000000196_01_text.json</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   datefield          publisher  \\\n",
       "0     1841.0  Privately printed   \n",
       "\n",
       "                                               title edition     place  \\\n",
       "0  The Poetical Aviary, with a bird's-eye view of...          Calcutta   \n",
       "\n",
       "      issuance  authors       first_pdf  number_volumes identifier  \\\n",
       "0  monographic  [A. A.]  lsidyv35c55757               1  000000196   \n",
       "\n",
       "        fulltext_filename  \n",
       "0  000000196_01_text.json  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_meta.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fulltext_filename</th>\n",
       "      <th>fulltext</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>000000196_01_text.json</td>\n",
       "      <td>THE POETICAL AVIARY, WITH A B I R D'S-E ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          fulltext_filename                                           fulltext\n",
       "150  000000196_01_text.json        THE POETICAL AVIARY, WITH A B I R D'S-E ..."
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_texts[df_texts[\"fulltext_filename\"] == '000000196_01_text.json']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>aleph system no.</th>\n",
       "      <th>country code</th>\n",
       "      <th>language code (008)</th>\n",
       "      <th>language code (041)</th>\n",
       "      <th>ddc</th>\n",
       "      <th>personal author</th>\n",
       "      <th>corporate author</th>\n",
       "      <th>title</th>\n",
       "      <th>edition</th>\n",
       "      <th>imprint</th>\n",
       "      <th>series</th>\n",
       "      <th>subjects</th>\n",
       "      <th>other personal authors</th>\n",
       "      <th>other corporate authors</th>\n",
       "      <th>dom id</th>\n",
       "      <th>type</th>\n",
       "      <th>genre</th>\n",
       "      <th>first_pdf</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>234</th>\n",
       "      <td>14846757</td>\n",
       "      <td>|||</td>\n",
       "      <td>eng</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A. A.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>The Poetical Aviary, with a bird's-eye view of...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Calcutta : Privately printed, 1841.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>lsidyv35c55757</td>\n",
       "      <td>poet</td>\n",
       "      <td>Poetry</td>\n",
       "      <td>lsidyv35c55757</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     aleph system no. country code language code (008) language code (041)  \\\n",
       "234          14846757          |||                 eng                 NaN   \n",
       "\n",
       "     ddc personal author corporate author  \\\n",
       "234  NaN           A. A.              NaN   \n",
       "\n",
       "                                                 title edition  \\\n",
       "234  The Poetical Aviary, with a bird's-eye view of...     NaN   \n",
       "\n",
       "                                 imprint series subjects  \\\n",
       "234  Calcutta : Privately printed, 1841.    NaN      NaN   \n",
       "\n",
       "    other personal authors  other corporate authors          dom id  type  \\\n",
       "234                    NaN                      NaN  lsidyv35c55757  poet   \n",
       "\n",
       "      genre       first_pdf  \n",
       "234  Poetry  lsidyv35c55757  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_extra[df_extra[\"first_pdf\"] == \"lsidyv35c55757\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Now we switch to the blackboard and model!**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tidy dataset: relational-model\n",
    "\n",
    "* Full view (for your curiosity): https://dbdiagram.io/d/5d06a4adfff7633dfc8e3a42\n",
    "* Reduced view (we here use this one): https://dbdiagram.io/d/5d06a5d0fff7633dfc8e3a47"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# first, join the extra metadata genre column to the metadata data frame. More details on joins in class 3.1.\n",
    "\n",
    "df_extra_genre = df_extra[[\"type\",\"genre\",\"first_pdf\"]]\n",
    "df_book = df_meta.join(df_extra_genre.set_index('first_pdf'), on='first_pdf')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>datefield</th>\n",
       "      <th>publisher</th>\n",
       "      <th>title</th>\n",
       "      <th>edition</th>\n",
       "      <th>place</th>\n",
       "      <th>issuance</th>\n",
       "      <th>authors</th>\n",
       "      <th>first_pdf</th>\n",
       "      <th>number_volumes</th>\n",
       "      <th>identifier</th>\n",
       "      <th>fulltext_filename</th>\n",
       "      <th>type</th>\n",
       "      <th>genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1841.0</td>\n",
       "      <td>Privately printed</td>\n",
       "      <td>The Poetical Aviary, with a bird's-eye view of...</td>\n",
       "      <td></td>\n",
       "      <td>Calcutta</td>\n",
       "      <td>monographic</td>\n",
       "      <td>[A. A.]</td>\n",
       "      <td>lsidyv35c55757</td>\n",
       "      <td>1</td>\n",
       "      <td>000000196</td>\n",
       "      <td>000000196_01_text.json</td>\n",
       "      <td>poet</td>\n",
       "      <td>Poetry</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   datefield          publisher  \\\n",
       "0     1841.0  Privately printed   \n",
       "\n",
       "                                               title edition     place  \\\n",
       "0  The Poetical Aviary, with a bird's-eye view of...          Calcutta   \n",
       "\n",
       "      issuance  authors       first_pdf  number_volumes identifier  \\\n",
       "0  monographic  [A. A.]  lsidyv35c55757               1  000000196   \n",
       "\n",
       "        fulltext_filename  type   genre  \n",
       "0  000000196_01_text.json  poet  Poetry  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_book.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fulltext_filename</th>\n",
       "      <th>fulltext</th>\n",
       "      <th>book_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000551646_01_text.json</td>\n",
       "      <td>' -■\" ' LiLitr-- )Wm&amp;, HISTORY OF THE...</td>\n",
       "      <td>000551646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>002674278_01_text.json</td>\n",
       "      <td>The Great Revolution of 1840. REMINISC...</td>\n",
       "      <td>002674278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>001975731_01_text.json</td>\n",
       "      <td>THE REAR-GUARD OF THE REVOLUTION. BY E...</td>\n",
       "      <td>001975731</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        fulltext_filename                                           fulltext  \\\n",
       "0  000551646_01_text.json           ' -■\" ' LiLitr-- )Wm&, HISTORY OF THE...   \n",
       "1  002674278_01_text.json          The Great Revolution of 1840. REMINISC...   \n",
       "2  001975731_01_text.json          THE REAR-GUARD OF THE REVOLUTION. BY E...   \n",
       "\n",
       "     book_id  \n",
       "0  000551646  \n",
       "1  002674278  \n",
       "2  001975731  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# second, add the book_id to the book_text dataframe\n",
    "\n",
    "df_book_text = df_texts.join(df_book[[\"identifier\",\"fulltext_filename\"]].set_index('fulltext_filename'), on='fulltext_filename')\n",
    "df_book_text = df_book_text.rename(columns={\"identifier\":\"book_id\"})\n",
    "df_book_text.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# third, pull our author information and create the author table and the author-book table\n",
    "\n",
    "author_id = 0 # this is a counter which provides for a distinct identifier to every author\n",
    "author_dict = OrderedDict()\n",
    "author_book_table = {\"book_id\":list(),\"author_id\":list()}\n",
    "for book_id, authors in df_book[[\"identifier\",\"authors\"]].values:\n",
    "    for author in authors:\n",
    "        if author not in author_dict.keys():\n",
    "            author_dict[author] = author_id\n",
    "            author_id += 1\n",
    "        author_book_table[\"book_id\"].append(book_id)\n",
    "        author_book_table[\"author_id\"].append(author_dict[author])\n",
    "        \n",
    "df_author_book = pd.DataFrame.from_dict(author_book_table)\n",
    "df_author = pd.DataFrame.from_dict({\"name\":[v for v in author_dict.keys()],\n",
    "                                   \"id\":[k for k in author_dict.values()]})\n",
    "df_author.set_index(\"id\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A. A.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Abbott, Evelyn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A'BECKETT, Gilbert Abbott.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          name\n",
       "id                            \n",
       "0                        A. A.\n",
       "1               Abbott, Evelyn\n",
       "2   A'BECKETT, Gilbert Abbott."
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_author.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>book_id</th>\n",
       "      <th>author_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000000196</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000004047</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000005382</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     book_id  author_id\n",
       "0  000000196          0\n",
       "1  000004047          1\n",
       "2  000005382          2"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_author_book.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop authors from df_books\n",
    "\n",
    "df_book.drop(columns=[\"authors\"], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Note: you don't need to do this: these dataframes are already there!*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# let's now save our data frames for future use\n",
    "\n",
    "root_folder = \"../data/bl_books/sample_tidy/\"\n",
    "df_book.to_csv(os.path.join(root_folder,\"df_book.csv\"), index=False)\n",
    "df_author.to_csv(os.path.join(root_folder,\"df_author.csv\"), index=False)\n",
    "df_author_book.to_csv(os.path.join(root_folder,\"df_author_book.csv\"), index=False)\n",
    "df_book_text.to_csv(os.path.join(root_folder,\"df_book_text.csv\"), index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## A last look at the tidy dataset\n",
    "\n",
    "**Questions**:\n",
    "* how many authors are there? Are there books with more than one author? And authors who published more than one book?\n",
    "* How many books per genre do we have?\n",
    "* What is the typical year of publication of our books?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SQL and relational databases\n",
    "\n",
    "Our tidy dataset is structured now as a relational database. \n",
    "\n",
    "SQL (Structured Query Language) is the general language to query such databases. https://en.wikipedia.org/wiki/SQL\n",
    "\n",
    "MySQL and Postgresql are common implementation of relational databases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.types import Integer\n",
    "engine = create_engine('sqlite://', echo=False)\n",
    "df_book.to_sql('books', con=engine, index=False, dtype={\"datefield\": Integer()})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1841, 'Privately printed', \"The Poetical Aviary, with a bird's-eye view of the English poets. [The preface signed: A. A.] Ms. notes\", '', 'Calcutta', 'monographic', 'lsidyv35c55757', 1, '000000196', '000000196_01_text.json', 'poet', 'Poetry'),\n",
       " (1888, 'Rivingtons', 'A History of Greece. Part I. From the earliest times to the Ionian Revolt', '', 'London', 'monographic', 'lsidyv376da437', 1, '000004047', '000004047_01_text.json', 'story', 'Prose'),\n",
       " (1847, 'Punch Office', 'The Comic History of England ... With ... coloured etchings, and ... woodcuts, by John Leech', '', 'London', 'monographic', 'lsidyv38b27a31', 1, '000005382', '000005382_01_text.json', 'story', 'Prose')]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.execute(\"SELECT * FROM books LIMIT 3\").fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a data frame from a DB table\n",
    "\n",
    "df_books_fromDB = pd.read_sql_query(\"SELECT * FROM books\", engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>datefield</th>\n",
       "      <th>publisher</th>\n",
       "      <th>title</th>\n",
       "      <th>edition</th>\n",
       "      <th>place</th>\n",
       "      <th>issuance</th>\n",
       "      <th>first_pdf</th>\n",
       "      <th>number_volumes</th>\n",
       "      <th>identifier</th>\n",
       "      <th>fulltext_filename</th>\n",
       "      <th>type</th>\n",
       "      <th>genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1841.0</td>\n",
       "      <td>Privately printed</td>\n",
       "      <td>The Poetical Aviary, with a bird's-eye view of...</td>\n",
       "      <td></td>\n",
       "      <td>Calcutta</td>\n",
       "      <td>monographic</td>\n",
       "      <td>lsidyv35c55757</td>\n",
       "      <td>1</td>\n",
       "      <td>000000196</td>\n",
       "      <td>000000196_01_text.json</td>\n",
       "      <td>poet</td>\n",
       "      <td>Poetry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1888.0</td>\n",
       "      <td>Rivingtons</td>\n",
       "      <td>A History of Greece. Part I. From the earliest...</td>\n",
       "      <td></td>\n",
       "      <td>London</td>\n",
       "      <td>monographic</td>\n",
       "      <td>lsidyv376da437</td>\n",
       "      <td>1</td>\n",
       "      <td>000004047</td>\n",
       "      <td>000004047_01_text.json</td>\n",
       "      <td>story</td>\n",
       "      <td>Prose</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1847.0</td>\n",
       "      <td>Punch Office</td>\n",
       "      <td>The Comic History of England ... With ... colo...</td>\n",
       "      <td></td>\n",
       "      <td>London</td>\n",
       "      <td>monographic</td>\n",
       "      <td>lsidyv38b27a31</td>\n",
       "      <td>1</td>\n",
       "      <td>000005382</td>\n",
       "      <td>000005382_01_text.json</td>\n",
       "      <td>story</td>\n",
       "      <td>Prose</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   datefield          publisher  \\\n",
       "0     1841.0  Privately printed   \n",
       "1     1888.0         Rivingtons   \n",
       "2     1847.0       Punch Office   \n",
       "\n",
       "                                               title edition     place  \\\n",
       "0  The Poetical Aviary, with a bird's-eye view of...          Calcutta   \n",
       "1  A History of Greece. Part I. From the earliest...            London   \n",
       "2  The Comic History of England ... With ... colo...            London   \n",
       "\n",
       "      issuance       first_pdf  number_volumes identifier  \\\n",
       "0  monographic  lsidyv35c55757               1  000000196   \n",
       "1  monographic  lsidyv376da437               1  000004047   \n",
       "2  monographic  lsidyv38b27a31               1  000005382   \n",
       "\n",
       "        fulltext_filename   type   genre  \n",
       "0  000000196_01_text.json   poet  Poetry  \n",
       "1  000004047_01_text.json  story   Prose  \n",
       "2  000005382_01_text.json  story   Prose  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_books_fromDB.head(3)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}